package com.bringway.p2p.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.global.util.UserUtil;
import com.bringway.p2p.dao.ZnDao;

import net.sf.json.JSONObject;

@Repository
public class ZnDaoImpl extends P2PJdbcDao implements ZnDao {
	
	Logger logger = Logger.getLogger(this.getClass().getName());

	@Override
	public List queryYjHmcList(JSONObject params) {
		int start = params.optInt("start", 0);
		int limit = params.optInt("limit", 10);
		String gyh = params.optString("gyh", "");
		String xm = params.optString("xm", "");
		String sjhm = params.optString("sjhm", "");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append(" SELECT * FROM T_ZHFW_OA_USER WHERE 1=1 ");
		if(StringUtils.isNotEmpty(gyh)){
			sql.append(" AND GYH =:GYH ");
			paramMap.put("GYH", gyh);
		}
		if(StringUtils.isNotEmpty(xm)){
			sql.append(" AND XM =:XM ");
			paramMap.put("XM", xm);
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND SJHM =:SJHM ");
			paramMap.put("SJHM", sjhm);
		}
		sql.append(" ORDER BY ID ASC ");
		if(0 != limit){
			sql.append(" LIMIT :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("queryYjHmcList sql is " + sql.toString());
		logger.info("queryYjHmcList paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getYjHmcCount(JSONObject params) {
		String gyh = params.optString("gyh", "");
		String xm = params.optString("xm", "");
		String sjhm = params.optString("sjhm", "");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append(" SELECT COUNT(*) FROM T_ZHFW_OA_USER WHERE 1=1 ");
		if(StringUtils.isNotEmpty(gyh)){
			sql.append(" AND GYH =:GYH ");
			paramMap.put("GYH", gyh);
		}
		if(StringUtils.isNotEmpty(xm)){
			sql.append(" AND XM =:XM ");
			paramMap.put("XM", xm);
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND SJHM =:SJHM ");
			paramMap.put("SJHM", sjhm);
		}
		logger.info("getYjHmcCount sql is " + sql.toString());
		logger.info("getYjHmcCount paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
	}

	@Override
	public void importHmc(List<String[]> excelList, JSONObject params) {
		for(int i=0;i<excelList.size();i++){
			String[] strT = excelList.get(i);
			StringBuffer sql = new StringBuffer();
			Map<String, String> paramMap = new HashMap<String, String>();
			sql.append(" insert into t_zhfw_oa_user(ID, DQ, BM, SS, ZW, ZJ, GYH, XM, RZSJ, ZZSJ, ZZTX, SFZZ, HT_START_DATE ");
			sql.append(" 	,HT_END_DATE, SFZHM, XB, CSRQ, AGE, JG, MZ, SFZ_ADDRESS, NOW_ADDRESS, SJHM, JJLXR, JJLXR_SJHM, BYYY ");
			sql.append("	,ZY ,ZGXL, SFJH, OA_TJR_GYH, TEAM_LEADER) ");
			sql.append(" values(:ID, :DQ, :BM, :SS, :ZW, :ZJ, :GYH, :XM, :RZSJ, :ZZSJ, :ZZTX, :SFZZ, :HT_START_DATE, :HT_END_DATE ");
			sql.append(" 	, :SFZHM, :XB, :CSRQ, :AGE, :JG, :MZ, :SFZ_ADDRESS, :NOW_ADDRESS, :SJHM, :JJLXR, :JJLXR_SJHM, :BYYY ");
			sql.append("	,:ZY ,:ZGXL, :SFJH, :OA_TJR_GYH, :TEAM_LEADER)");
			paramMap.put("ID", this.getStrArrValueByIndex(strT, 1));
			paramMap.put("DQ", this.getStrArrValueByIndex(strT, 2));
			paramMap.put("BM", this.getStrArrValueByIndex(strT, 3));
			paramMap.put("SS", this.getStrArrValueByIndex(strT, 4));
			paramMap.put("ZW", this.getStrArrValueByIndex(strT, 5));
			paramMap.put("ZJ", this.getStrArrValueByIndex(strT, 6));
			paramMap.put("GYH", this.getStrArrValueByIndex(strT, 7));
			paramMap.put("XM", this.getStrArrValueByIndex(strT, 8));
			paramMap.put("RZSJ", this.getStrArrValueByIndex(strT, 9));
			paramMap.put("ZZSJ", this.getStrArrValueByIndex(strT, 10));
			paramMap.put("ZZTX", this.getStrArrValueByIndex(strT, 11));
			paramMap.put("SFZZ", this.getStrArrValueByIndex(strT, 12));
			paramMap.put("HT_START_DATE", this.getStrArrValueByIndex(strT, 13));
			paramMap.put("HT_END_DATE", this.getStrArrValueByIndex(strT, 14));
			paramMap.put("SFZHM", this.getStrArrValueByIndex(strT, 15));
			paramMap.put("XB", this.getStrArrValueByIndex(strT, 16));
			paramMap.put("CSRQ", this.getStrArrValueByIndex(strT, 17));
			paramMap.put("AGE", this.getStrArrValueByIndex(strT, 18));
			paramMap.put("JG", this.getStrArrValueByIndex(strT, 19));
			paramMap.put("MZ", this.getStrArrValueByIndex(strT, 20));
			paramMap.put("SFZ_ADDRESS", this.getStrArrValueByIndex(strT, 21));
			paramMap.put("NOW_ADDRESS", this.getStrArrValueByIndex(strT, 22));
			paramMap.put("SJHM", this.getStrArrValueByIndex(strT, 23));
			paramMap.put("JJLXR", this.getStrArrValueByIndex(strT, 24));
			paramMap.put("JJLXR_SJHM", this.getStrArrValueByIndex(strT, 25));
			paramMap.put("BYYY", this.getStrArrValueByIndex(strT, 26));
			paramMap.put("ZY", this.getStrArrValueByIndex(strT, 27));
			paramMap.put("ZGXL", this.getStrArrValueByIndex(strT, 28));
			paramMap.put("SFJH", this.getStrArrValueByIndex(strT, 29));
			paramMap.put("OA_TJR_GYH", this.getStrArrValueByIndex(strT, 30));
			paramMap.put("TEAM_LEADER", this.getStrArrValueByIndex(strT, 31));
			logger.info("importHmc 第"+i+"行 ：sql is " + sql.toString());
			logger.info("importHmc 第"+i+"行 ：paramMap is " + paramMap.toString());
			this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
		}
		
	}
	
	public String getStrArrValueByIndex(String[] strT, int index){
		int length = strT.length;
		if(index > length){
			return "";
		}else{
			return strT[index-1];
		}
	}

	@Override
	public void truncateHmc(JSONObject params) {
//		String sql = " truncate t_zhfw_oa_user ";
		String sql = " delete from t_zhfw_oa_user ";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		logger.info("truncateHmc sql is : " + sql);
		this.getNamedParameterJdbcTemplate().update(sql,paramMap);
	}

	@Override
	public void delHmcUser(String id) {
		StringBuffer sql1 = new StringBuffer();
		Map<String, Object> paramMap1 = new HashMap<String, Object>();
		sql1.append(" insert into t_zhfw_oa_user_log( ID , OA_USER_ID, DQ , BM , SS , ZW , ZJ , GYH , XM ,  RZSJ , ZZSJ , ZZTX , SFZZ ,  HT_START_DATE ,  HT_END_DATE ,  SFZHM , XB ,  CSRQ , AGE , JG ,  MZ ,  SFZ_ADDRESS , NOW_ADDRESS , SJHM , JJLXR , JJLXR_SJHM ,  BYYY ,  ZY , ZGXL , SFJH , OA_TJR_GYH ,TEAM_LEADER ");
		sql1.append("	, OPER_USERID, OPER_DATE, OPER_TYPE )  ");
		sql1.append(" select  UUID(), ID , DQ , BM , SS , ZW , ZJ , GYH , XM ,  RZSJ , ZZSJ , ZZTX , SFZZ ,  HT_START_DATE ,  HT_END_DATE ,  SFZHM , XB ,  CSRQ , AGE , JG ,  MZ ,  SFZ_ADDRESS , NOW_ADDRESS , SJHM , JJLXR , JJLXR_SJHM ,  BYYY ,  ZY , ZGXL , SFJH , OA_TJR_GYH ,TEAM_LEADER  ");
		sql1.append(" 	,'"+UserUtil.getCurrentUserId()+"', now(), '0' ");
		sql1.append("  from t_zhfw_oa_user ");
		sql1.append(" where id = :ID ");
		paramMap1.put("ID", id);
		logger.info("delHmcUser sql1 is : " + sql1);
		logger.info("delHmcUser paramMap1 is : " + paramMap1);
		this.getNamedParameterJdbcTemplate().update(sql1.toString(), paramMap1);
		
		StringBuffer sql2 = new StringBuffer();
		sql2.append(" delete from t_zhfw_oa_user where id = :ID ");
		logger.info("delHmcUser sql2 is : " + sql2);
		logger.info("delHmcUser paramMap1 is : " + paramMap1);
		this.getNamedParameterJdbcTemplate().update(sql2.toString(), paramMap1);
	}

	@Override
	public synchronized void addHmcUser(JSONObject params) {
		String A_DQ = params.optString("A_DQ","");
		String A_BM = params.optString("A_BM","");
		String A_SS = params.optString("A_SS","");
		String A_ZW = params.optString("A_ZW","");
		String A_ZJ = params.optString("A_ZJ","");
		String A_GYH = params.optString("A_GYH","");
		String A_XM = params.optString("A_XM","");
		String A_RZSJ = params.optString("A_RZSJ","");
		String A_SJHM = params.optString("A_SJHM","");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		int maxId = this.getNamedParameterJdbcTemplate().queryForObject("SELECT max(id) FROM T_ZHFW_OA_USER", paramMap, Integer.class);
		sql.append(" insert into T_ZHFW_OA_USER(ID,DQ,BM,SS,ZW,ZJ,GYH,XM,RZSJ,SJHM) ");
		sql.append(" values(:ID,:DQ,:BM,:SS,:ZW,:ZJ,:GYH,:XM,:RZSJ,:SJHM) ");
		paramMap.put("ID", ++maxId);
		paramMap.put("DQ", A_DQ);
		paramMap.put("BM", A_BM);
		paramMap.put("SS", A_SS);
		paramMap.put("ZW", A_ZW);
		paramMap.put("ZJ", A_ZJ);
		paramMap.put("GYH", A_GYH);
		paramMap.put("XM", A_XM);
		paramMap.put("RZSJ", A_RZSJ);
		paramMap.put("SJHM", A_SJHM);
		logger.info("addHmcUser sql is : " + sql);
		logger.info("addHmcUser paramMap is : " + paramMap);
		this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
	}

	@Override
	public boolean checkGyh(JSONObject params) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append(" SELECT COUNT(*) FROM T_ZHFW_OA_USER ");
		sql.append(" WHERE GYH = :GYH ");
		paramMap.put("GYH", params.optString("A_GYH",""));
		logger.info("checkGyh sql is : " + sql);
		logger.info("checkGyh paramMap is : " + paramMap);
		int ct = this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
		if(ct > 0 ){//已存在
			return true;
		}else{
			return false;
		}
	}

	
}
